/**
 * 面板统计数据
 */
import db, { mysql } from 'src-ssr/db/connect';
import * as tools from 'src-ssr/util/common';
import shortid from 'shortid';
import { Sequelize, Op } from 'sequelize';
import models from 'src-ssr/db/sequelize';
import moment from 'moment';

// 面板统计数据
export const tenantAbstract = async (req, res) => {
  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);

  // 上架货物
  const [sellMaterial] = await mysql.query(`SELECT IFNULL(SUM(sell=1), 0) sell, COUNT(*) total
    FROM t_materials
    WHERE tenant_id = ?`, [user.tid]);

  // 上架分类
  const [sellCatalog] = await mysql.query(`SELECT IFNULL(SUM(sell=1), 0) sell, COUNT(*) total
    FROM t_material_catalog
    WHERE tenant_id = ? AND
    parent_id = (SELECT id FROM t_material_catalog WHERE tenant_id = ? AND parent_id = -1)`, [user.tid, user.tid]);

  // 销售单类型
  const [sales] = await mysql.query(`SELECT m.type, COUNT(DISTINCT m.uid) bill_qty, SUM(d.money) money,
    COUNT(DISTINCT m.consumer_uid) consumer_qty, COUNT(DISTINCT m.customer_uid) customer_qty
    FROM t_sale_details d
    LEFT JOIN t_sales m ON d.master_uid = m.uid
    WHERE d.tenant_id = ? AND m.status = 'normal'
    GROUP BY m.type`, [user.tid]);
  const shopSale = sales.find(v => v.type === 'sale') || { qty: 0, money: 0, consumer_qty: 0, customer_qty: 0 };
  const enterSale = sales.find(v => v.type === 'enter') || { qty: 0, money: 0, consumer_qty: 0, customer_qty: 0 };

  // 配送统计
  const [deliveries] = await mysql.query(`SELECT COUNT(*) total, ROUND(AVG(d.create_time-s.create_time)) avg_time
    FROM t_delivery_details d
    LEFT JOIN t_sale_details s ON d.sale_detail_uid=s.uid
    LEFT JOIN t_sales m ON s.master_uid = m.uid
    WHERE d.tenant_id = ? AND m.status = 'normal'
    AND d.create_time>UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY))`, [user.tid]);

  // 待配送数量
  const [waitDel] = await mysql.query(`SELECT COUNT(*) qty
    FROM t_sale_details s
    LEFT JOIN t_sales m ON s.master_uid = m.uid
    WHERE s.tenant_id = ? AND s.amount>IFNULL((SELECT SUM(deliver_amount)
    FROM t_delivery_details d
    WHERE s.uid=d.sale_detail_uid), 0)
    AND m.status = 'normal'`, [user.tid]);

  // 最近30天top10热销商品
  const [currTopTen] = await mysql.query(`SELECT bin_to_uuid(d.material_uid, 1) uid, tm.material_name_model, SUM(d.money) money
    FROM t_sale_details d
    LEFT JOIN t_sales m ON d.master_uid=m.uid
    LEFT JOIN t_materials tm ON d.material_uid=tm.uid
    WHERE d.tenant_id = ? AND m.create_time>UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) AND m.status = 'normal'
    GROUP BY d.material_uid, tm.material_name_model
    ORDER BY money DESC
    LIMIT 10`, [user.tid]);

  // 最近60-30天top10热销商品
  const [lastTopTen] = await mysql.query(`SELECT bin_to_uuid(d.material_uid, 1) uid, tm.material_name_model, SUM(d.money) money
    FROM t_sale_details d
    LEFT JOIN t_sales m ON d.master_uid=m.uid
    LEFT JOIN t_materials tm ON d.material_uid=tm.uid
    WHERE d.tenant_id = ? AND m.create_time>UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 60 DAY))
    AND m.create_time<UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) AND m.status = 'normal'
    GROUP BY d.material_uid, tm.material_name_model
    ORDER BY money DESC
    LIMIT 10`, [user.tid]);
  // 30天Top10销售商品
  const topTenMaterial = currTopTen.map((v, i) => {
    const lastIdx = lastTopTen.findIndex(r => r.uid === v.uid);
    return {
      rank: i + 1, // 本期排名
      material_name_model: v.material_name_model, // 名称规格
      money: Number(v.money).toFixed(2), // 销售金额
      change: (lastIdx < 0 ? 10 : lastIdx) - i // 排名变动
    }
  });

  // 最近30天top10客户
  const [curTopTenCus] = await mysql.query(`SELECT CONCAT(IFNULL(bin_to_uuid(m.consumer_uid, 1), ''), IFNULL(bin_to_uuid(m.customer_uid, 1), '')) uid, m.type,
    IF(m.type='shop', mc.nickname, tc.nickname) nickname, SUM(d.money) money
    FROM t_sale_details d
    LEFT JOIN t_sales m ON d.master_uid=m.uid
    LEFT JOIN m_consumers mc ON m.consumer_uid=mc.uid
    LEFT JOIN t_customers tc ON m.customer_uid=tc.uid
    WHERE d.tenant_id = ? AND m.create_time>UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) AND m.status = 'normal'
    GROUP BY CONCAT(IFNULL(bin_to_uuid(m.consumer_uid, 1), ''), IFNULL(bin_to_uuid(m.customer_uid, 1), '')),
    m.type, IF(m.type='shop', mc.nickname, tc.nickname)
    ORDER BY money DESC
    LIMIT 10`, [user.tid]);

  const topTenCustomer = curTopTenCus.map((v, i) => {
    return {
      rank: i + 1, // 排名
      type: v.type, // 类型
      nickname: v.nickname, // 客户名称
      money: Number(v.money).toFixed(2) // 消费金额
    }
  });

  res.json({
    flop: {
      sellMat: Number(sellMaterial[0].sell), // 1、在售货物数量
      sellMatPer: ~~(sellMaterial[0].sell / sellMaterial[0].total * 100), // 在售货物百分比
      sellCat: Number(sellCatalog[0].sell), // 2、在售分类数量
      sellCatPer: ~~(sellCatalog[0].sell / sellCatalog[0].total * 100), // 在售分类百分比
      shopEnterMoney: `${shopSale.money > 0 ? 100 : 0}:${enterSale.money > 0 && shopSale.money > 0 ? ~~(enterSale.money / shopSale.money * 100) : (enterSale.money > 0 ? 100 : 0)}`, // 3、线上销售额:线下销售额
      // shopEnterQty: `100:${~~(enterSale.customer_qty / shopSale.consumer_qty * 100)}`, // 4、线上客户数量:线下客户数量
      shopPrice: shopSale.consumer_qty ? (shopSale.money / shopSale.consumer_qty).toFixed(2) : '0.00', // 4、线上客单价
      enterPrice: enterSale.customer_qty ? (enterSale.money / enterSale.customer_qty).toFixed(2) : '0.00', // 5、线下客单价
      deliveryQty: deliveries[0].total, // 6、配送总量
      deliveryTime: Number((deliveries[0].avg_time / 60 / 60).toFixed(1)), // 7、配送平均时长
      waitDelQty: waitDel[0].qty // 8、待配送数量
    },
    topTenMaterial, // top10热销商品
    topTenCustomer // top10客户
  });
};

// 配送大屏数据
export const deliveryData = async (req, res) => {
  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);

  // 在售货物
  const [sellMaterial] = await mysql.query(`SELECT IFNULL(SUM(sell=1), 0) sell, COUNT(*) total
    FROM t_materials
    WHERE tenant_id = ?`, [user.tid]);
  // 配送总量
  const [deliveries] = await mysql.query(`SELECT COUNT(*) total
    FROM t_delivery_details
    WHERE tenant_id = ?`, [user.tid]);

  // 待配送
  const [toBeDelivered] = await mysql.query(`SELECT bin_to_uuid(d.uid, 1) uid, m.bill_no, m.type, tm.material_name_model,
    d.amount-IFNULL((SELECT SUM(deliver_amount)
      FROM t_delivery_details td
      WHERE d.uid=td.sale_detail_uid), 0) left_amount, tm.material_unit
    FROM t_sale_details d
    LEFT JOIN t_sales m ON d.master_uid=m.uid
    LEFT JOIN t_materials tm ON d.material_uid=tm.uid
    WHERE d.tenant_id = ? AND m.status = 'normal' AND d.amount>IFNULL((SELECT SUM(deliver_amount)
    FROM t_delivery_details td
    WHERE d.uid=td.sale_detail_uid), 0)`, [user.tid]);
  for (const row of toBeDelivered) {
    row.type = global.dictObj[user.tid].saleType[row.type].label;
  }

  // 今日已配送
  const [delivered] = await mysql.query(`SELECT sm.bill_no, sm.type, tm.material_name_model, d.deliver_amount, m.logistics, tm.material_unit
    FROM t_delivery_details d
    LEFT JOIN t_deliveries m ON d.master_uid=m.uid
    LEFT JOIN t_sale_details sd ON d.sale_detail_uid=sd.uid
    LEFT JOIN t_sales sm ON sd.master_uid=sm.uid
    LEFT JOIN t_materials tm ON sd.material_uid=tm.uid
    WHERE d.tenant_id = ? AND m.create_time>UNIX_TIMESTAMP(CURDATE())`, [user.tid]);

  for (const row of delivered) {
    row.type = global.dictObj[user.tid].saleType[row.type].label;
  }

  res.json({
    flop: {
      sellMaterial: Number(sellMaterial[0].sell),
      deliveryQry: Number(deliveries[0].total),
      toBeDelivered: toBeDelivered.length,
      delivered: delivered.length
    },
    toBeDelivered,
    delivered
  });
};
